package secondriver.heathfood.util.unused;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import secondriver.heathfood.util.LogUtil;

import java.util.*;

public class SimpleSQLiteHelper extends SQLiteOpenHelper {

    private final LogUtil logUtil = LogUtil.getLogger(SimpleSQLiteHelper.class.getName());

    private Queue<String> createScripts = new LinkedList<String>();
    private Queue<String> upgradeScripts = new LinkedList<String>();
    private Queue<String> downgradeScripts = new LinkedList<String>();

    public static final int DEFAULT_DB_VERSION = 1;

    private static volatile SimpleSQLiteHelper instance;

    public static Creator Creator(Context context) {
        return new Creator(context);
    }

    /**
     * 创建SimpleSQLiteHelper实例辅助工具类
     */
    public static class Creator {

        private final Context context;
        private String name;
        private CursorFactory factory;
        private int version = DEFAULT_DB_VERSION;
        private DatabaseErrorHandler errorHandler;

        private Queue<String> createScripts = new LinkedList<String>();
        private Queue<String> upgradeScripts = new LinkedList<String>();
        private Queue<String> downgradeScripts = new LinkedList<String>();

        private Creator(Context context) {
            this.context = context;
        }

        public Creator name(String name) {
            this.name = name;
            return this;
        }

        public Creator cursorFactory(CursorFactory factory) {
            this.factory = factory;
            return this;
        }

        public Creator version(int version) {
            this.version = version;
            return this;
        }

        public Creator databaseErrorHandler(DatabaseErrorHandler errorHandler) {
            this.errorHandler = errorHandler;
            return this;
        }

        public synchronized Creator addCreateScript(String... scripts) {
            if (null == scripts || scripts.length == 0) {
                return this;
            }
            for (String s : scripts) {
                createScripts.add(s);
            }
            return this;
        }

        public synchronized Creator addUpgradeScript(String... scripts) {
            if (null == scripts || scripts.length == 0) {
                return this;
            }
            for (String s : scripts) {
                upgradeScripts.add(s);
            }
            return this;
        }

        public synchronized Creator addDowngradeScript(String... scripts) {
            if (null == scripts || scripts.length == 0) {
                return this;
            }
            for (String s : scripts) {
                downgradeScripts.add(s);
            }
            return this;
        }

        public SimpleSQLiteHelper create() {
            if (null == instance) {
                synchronized (SimpleSQLiteHelper.class) {
                    if (null == instance) {
                        instance = new SimpleSQLiteHelper(context, name, factory,
                                version, errorHandler);
                        instance.createScripts = createScripts;
                        instance.upgradeScripts = upgradeScripts;
                        instance.downgradeScripts = downgradeScripts;
                    }
                }
            }
            return instance;
        }
    }

    private SimpleSQLiteHelper(Context context, String name, CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);
    }


    /**
     *
     *
     * @param context       context environment
     * @param dbName        database name
     * @param cursorFactory
     * @param dbVersion     database version number
     * @param errorHandler
     * @return
     */
    /**
     * 获取SQLite Help对象，全局唯一实例
     *
     * @param context          上下文
     * @param dbName           数据库名
     * @param cursorFactory    游标工厂类
     * @param dbVersion        数据库版本号 >=1
     * @param errorHandler     错误处理类
     * @param createScripts    创建数据库时初始化脚本，脚本要严格控制顺序
     * @param upgradeScripts   数据库版本更新时初始化脚本，脚本要严格控制顺序
     * @param downgradeScripts 数据库版本降级时初始化脚本，脚本要严格控制顺序
     * @return SimpleSQLiteHelper 全局唯一实例
     */
    public static SimpleSQLiteHelper getInstance(
            Context context,
            String dbName,
            CursorFactory cursorFactory,
            int dbVersion,
            DatabaseErrorHandler errorHandler,
            String[] createScripts,
            String[] upgradeScripts,
            String[] downgradeScripts
    ) {
        return new Creator(context).name(dbName).cursorFactory(cursorFactory)
                .version(dbVersion).databaseErrorHandler(errorHandler)
                .addCreateScript(createScripts)
                .addUpgradeScript(upgradeScripts)
                .addDowngradeScript(downgradeScripts).create();
    }

    /**
     * 获取SQLite Help对象，全局唯一实例
     *
     * @param context       上下文
     * @param dbName        数据库名
     * @param cursorFactory 游标工厂类
     * @param dbVersion     数据库版本号 >=1
     * @param errorHandler  错误处理类
     * @return SimpleSQLiteHelper 全局唯一实例
     */
    public static SimpleSQLiteHelper getInstance(
            Context context,
            String dbName,
            CursorFactory cursorFactory,
            int dbVersion,
            DatabaseErrorHandler errorHandler
    ) {
        return getInstance(context, dbName, cursorFactory, dbVersion, errorHandler, new String[]{},
                new String[]{}, new String[]{});
    }

    /**
     * Android系统自动调用，当配置数据库连接时，启禁数据库特性配置
     *
     * @param db 数据库对象
     */
    @Override
    public void onConfigure(SQLiteDatabase db) {
    }

    /**
     * Android系统自动调用,初始生成数据库时调用
     * <p>
     * 初始化数据库表和初始化数据
     *
     * @param db 数据库对象
     */
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.beginTransaction();
        try {
            String value;
            while (null != (value = createScripts.poll())) {
                logUtil.info(value);
                db.execSQL(value);
            }
            db.setTransactionSuccessful();
        } catch (Exception e) {
            logUtil.error("", e);
        } finally {
            db.endTransaction();
        }
    }

    /**
     * Android系统自动调用，数据库当前版本高于原版本是调用
     * <p>
     * 数据库新版本由创建该对象时指定, oldVersion < newVersion
     *
     * @param db         数据库对象
     * @param oldVersion 旧版本号
     * @param newVersion 新版本号
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < newVersion) {
            db.beginTransaction();
            try {
                String value;
                while (null != (value = upgradeScripts.poll())) {
                    logUtil.info(value);
                    db.execSQL(value);
                }
                db.setTransactionSuccessful();
            } catch (Exception e) {
                logUtil.error("", e);
            } finally {
                db.endTransaction();
            }
        }
    }


    /**
     * Android系统自动调用，数据库当前版本低于原版本是调用
     * <p>
     * oldVersion > newVersion
     *
     * @param db
     * @param oldVersion
     * @param newVersion
     */
    @Override
    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        //如果实现该方法，则调用父类方法
        super.onDowngrade(db, oldVersion, newVersion);
    }

    /**
     * Android系统自动调用，数据库打开
     * <p>
     * 如果要对数据库进行更新操作需要检查数据库的可读写性
     *
     * @param db
     */
    @Override
    public void onOpen(SQLiteDatabase db) {
    }


    /**
     * 插入数据
     *
     * @param tableName 数据表
     * @param cv        要插入的数据行记录内容由key-value（FieldName-FieldValue）构成
     * @return 插入数据记录的主键ID，返回-1表示插入数据失败
     */
    public long insert(String tableName, ContentValues cv) {
        SQLiteDatabase db = this.getWritableDatabase();
        long id = db.insert(tableName, null, cv);
        db.close();
        return id;
    }

    /**
     * 更具指定条件插入和更新数据
     *
     * @param tableName
     * @param cv
     * @param whereClaus
     * @param whereArgs
     * @return 更新成功返回0, 更新失败返回-1, 插入返回主键_id的值
     */
    public long insertOrUpdate(String tableName, ContentValues cv, String whereClaus, String... whereArgs) {
        StringBuilder sqlBuilder = new StringBuilder("SELECT COUNT(*) AS count FROM ")
                .append(tableName);
        if (whereClaus != null && !"".equals(whereClaus)) {
            sqlBuilder.append(" WHERE ")
                    .append(whereClaus);
        }
        String sql = sqlBuilder.toString();
        logUtil.debug(sql);

        SQLiteDatabase db = this.getWritableDatabase();
        db.beginTransaction();
        long rs = -1;
        try {
            Cursor cursor = db.rawQuery(sql, whereArgs);
            if (null != cursor && cursor.moveToFirst()) {
                int countIndex = cursor.getColumnIndex("count");
                if (cursor.getInt(countIndex) == 1) {
                    cursor.close();
                    int effectRows = db.update(tableName, cv, whereClaus, whereArgs);
                    logUtil.debug("update table=%s data=%s effect rows=%d .", tableName, cv.toString(), effectRows);
                    rs = effectRows == 1 ? 0 : -1;
                    db.setTransactionSuccessful();
                    return rs;
                }
            }
            rs = db.insert(tableName, null, cv);
            logUtil.debug("insert table=%s data=%s .", tableName, cv.toString());
            db.setTransactionSuccessful();
        } catch (Exception e) {
            logUtil.error("exception ", e);
        } finally {
            db.endTransaction();
            db.close();
        }
        return rs;
    }

    /**
     * 更新数据
     * <p>
     * UPDATE table_name SET f1=v1, f2=v2
     *
     * @param tableName 数据表
     * @param cv        要更新的数据行记录内容由key-value（FieldName-FieldValue）构成
     * @return 更新操作受影响的行数
     */
    public int update(String tableName, ContentValues cv) {
        return update(tableName, cv, null, null);
    }

    /**
     * 更新数据
     * <p>
     * UPDATE table_name SET f1=v1, f2=v2 WHERE f3=? and f4=?
     *
     * @param tableName  数据表
     * @param cv         要更新的数据行记录内容由key-value（FieldName-FieldValue）构成
     * @param whereClaus 更新条件语句
     * @param whereArgs  更新条件语句中占位符参数对应的参数值列表
     * @return 更新操作受影响的行数
     */
    public int update(String tableName, ContentValues cv, String whereClaus, String... whereArgs) {
        SQLiteDatabase db = this.getWritableDatabase();
        try {
            int rows = db.update(tableName, cv, whereClaus, whereArgs);
            return rows;
        } catch (Exception e) {
            logUtil.error("update ", e);
            return 0;
        } finally {
            db.close();
        }
    }


    /**
     * 查询指定条件的记录
     * <p>
     * SELECT * FROM table_name WHERE f4 = ?
     *
     * @param tableName  数据库表
     * @param whereField 条件字段名
     * @param fieldValue 条件字段值
     * @return 所有字段查询结果集合
     */
    public List<ContentValues> query(String tableName, String whereField, String fieldValue) {
        SQLiteDatabase db = this.getReadableDatabase();
        return query(tableName, null, whereField, fieldValue);
    }

    /**
     * 查询指定条件的记录
     * <p>
     * SELECT f1, f2, f3 FROM table_name WHERE f4 = ?
     *
     * @param tableName  数据库表
     * @param fields     查询结果指定字段集合
     * @param whereField 条件字段名
     * @param fieldValue 条件字段值
     * @return 指定字段查询结果集合
     */
    public List<ContentValues> query(String tableName, String[] fields, String whereField, String fieldValue) {
        SQLiteDatabase db = this.getReadableDatabase();
        final Cursor cursor = db.query(tableName, fields, whereField + " = ?",
                new String[]{fieldValue}, null, null, null);
        List<ContentValues> contentValuesList = this.wrapperResult(cursor);
        cursor.close();
        db.close();
        return contentValuesList;
    }


    /**
     * 删除指定条件的记录
     * <p>
     * DELETE FROM table_name WHERE f1 = ?
     *
     * @param tableName  数据库表
     * @param whereField 条件字段名
     * @param fieldValue 条件字段值
     * @return 删除操作受影响的行数
     */
    public int delete(String tableName, String whereField, String fieldValue) {
        SQLiteDatabase db = this.getWritableDatabase();
        int rows = db.delete(tableName, whereField + "=?",
                new String[]{fieldValue});
        db.close();
        return rows;
    }

    private List<ContentValues> wrapperResult(final Cursor cursor) {
        List<ContentValues> list = new ArrayList<ContentValues>();
        while (cursor.moveToNext()) {
            ContentValues cv = new ContentValues();
            int columnCount = cursor.getColumnCount();
            for (int index = 0; index < columnCount; index++) {
                String key = cursor.getColumnName(index);
                String value = cursor.getString(index);
                cv.put(key, value);
            }
            list.add(cv);
        }
        return list;
    }
}
